# -*- coding: utf-8 -*-

from java.lang import Class
from java.sql import DriverManager

from CSVTools import CSVData
from unicodedata import normalize

class RebidesDB:    

    def __init__( self ):
        pass
    pass
    
    ##http://www.python.org.br/wiki/RemovedorDeAcentos
    def normalizeString( self, text, codif='utf-8' ):
        return normalize( 'NFKD', text.decode( codif ) ).encode( 'ASCII','ignore' )
    pass
    
    def addIntoDatabase( self, csvName, databaseName, tableName ):
        
        data = CSVData().readCSV( csvName )

        for i in range( 0, len( data ) ):
            for j in range( 0, len( data[ i ] ) ):
                data[ i ][ j ] = self.normalizeString( data[ i ][ j ] )
                pass
            pass
        
        Class.forName( "org.sqlite.JDBC" )
        connection = DriverManager.getConnection( "jdbc:sqlite:%s" % databaseName )
        statement = connection.createStatement()
        statement.executeUpdate( '''create table if not exists "%s" ("%s" text, "%s" text, "%s" text, "%s" text, "%s" text, "%s" text, "%s" text, "%s" text, "%s" text)''' % (tableName, data[0][0], data[0][1], data[0][2], data[0][3], data[0][4],data[0][5], data[0][6], data[0][7], data[0][8] ) )
                
        for i in range( 1, len ( data ) ):
            statement.executeUpdate( '''insert into %s ( "Codigo do Docente", "Nome", "Ultimo Grau", "Curso", "Categoria", "Regime de Tempo", "Ano Lectivo", "Tipo de Estabelecimento", "Nome do Estabelecimento" ) values ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")''' % (tableName, data[ i ][ 0 ].replace( "\"", "'" ), data[ i ][ 1 ].replace( "\"", "'" ), data[ i ][ 2 ].replace( "\"", "'" ), data[ i ][ 3 ].replace( "\"", "'" ),data[ i ][ 4 ].replace( "\"", "'" ), data[ i ][ 5 ].replace( "\"", "'" ), data[ i ][ 6 ].replace( "\"", "'" ), data[ i ][ 7 ].replace( "\"", "'" ), data[ i ][ 8 ].replace( "\"", "'" ) ) )
            pass
        connection.close()
        pass
    pass

    def loadDatabaseRebides( self, databaseName ):
        
        Class.forName( "org.sqlite.JDBC" )
        connection = DriverManager.getConnection( "jdbc:sqlite:%s" % databaseName )
        statement = connection.createStatement()
        rs = statement.executeQuery( '''select * from teachers''' )
        
        data = []
        count = 0
        while ( rs.next() ):

            data.append( [ rs.getString(1) ] )
            data[ count ].append( rs.getString( 2 ) )
            data[ count ].append( rs.getString( 3 ) )
            data[ count ].append( rs.getString( 4 ) )
            data[ count ].append( rs.getString( 5 ) )
            data[ count ].append( rs.getString( 6 ) )
            data[ count ].append( rs.getString( 7 ) )
            data[ count ].append( rs.getString( 8 ) )
            data[ count ].append( rs.getString( 9 ) )
            count += 1
            pass
        rs.close()
        
        names = []
        rs = statement.executeQuery( '''select distinct "Nome do Estabelecimento" from teachers''' )
        
        while (rs.next()):
            names.append( rs.getString( 1 ) )
            pass


        acad_years = []
        rs = statement.executeQuery( '''select distinct "Ano Lectivo" from teachers''' )
        
        while (rs.next()):
            acad_years.append( rs.getString( 1 ) )
            pass

        
        categorys = []
        rs = statement.executeQuery( '''select distinct "Categoria" from teachers''' )
        
        while (rs.next()):
            categorys.append( rs.getString( 1 ) )
            pass


        grades = []
        rs = statement.executeQuery( '''select distinct "Ultimo Grau" from teachers''' )
        
        while (rs.next()):
            grades.append( rs.getString( 1 ) )
            pass
        
        connection.close()
        return [ data, names, acad_years, categorys, grades ]
    pass